
package com.probiz.estore.sales.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.probiz.estore.catalog.dao.ProductCategoryDao;
import com.probiz.estore.common.model.catalog.ProductCategory;
import com.probiz.estore.common.model.product.Product;
import com.probiz.estore.core.dao.impl.HibernateGenericDaoImpl;
import com.probiz.estore.sales.SalesConstants;
import com.probiz.estore.sales.dao.AutoEvalRecommendationDao;

public class AutoEvalRecommendationDaoImpl extends
		HibernateGenericDaoImpl<Product> implements AutoEvalRecommendationDao {
	private ProductCategoryDao productCategoryDao = null;
	
	public void setProductCategoryDao(ProductCategoryDao productCategoryDao) {
		this.productCategoryDao = productCategoryDao;
	}

	// 自动评估 根据[源Id=categoryId]获得特定范围的推荐产品(不能重复,而且必须是激活的),采用的推荐规则是：购买数量排序
	// tip: sourceId -> ProductCategory -> Product -> ProductStat = {product}
	public List<Product> getBuyCountProductsBySourceId(final Integer sourceId,
			final int firstResult, final int maxResults) {
		final ProductCategory category = productCategoryDao.getById(sourceId);
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer sb = new StringBuffer(200);
				sb.append("select distinct(p) from Product p  where ");
				sb.append(" p.status = 1");
				sb.append(" and p.category.categoryPath like :path");
				sb.append(" order by p.productStat.buyCount desc , p.productId desc");
				Query query = session.createQuery(sb.toString());
				query.setString("path", category.getCategoryPath() + "%");
				query.setFirstResult(firstResult);
				if (maxResults != -1)
					query.setMaxResults(maxResults);
				List<Product> rows = (List<Product>) query.list();

				return rows;
			}

		});
		return list;
	}

	// 自动评估 根据[源Id=categoryId]获得特定范围的推荐产品(不能重复,而且必须是激活的),采用的推荐规则是：新产品，发布时间排序
	// tip: sourceId -> ProductCategory -> Product = {product}
	public List<Product> getNewProductsBySourceId(final Integer sourceId,
			final int firstResult, final int maxResults) {
		final ProductCategory category = productCategoryDao.getById(sourceId);
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer sb = new StringBuffer(200);
				sb.append("select distinct(p) from Product p where ");
				sb.append(" p.status = 1");
				sb.append(" and p.category.categoryPath like :path");
				sb.append(" order by p.productId desc");
				Query query = session.createQuery(sb.toString());
				query.setString("path", category.getCategoryPath() + "%");
				query.setFirstResult(firstResult);
				if (maxResults != -1)
					query.setMaxResults(maxResults);
				List<Product> rows = (List<Product>) query.list();

				return rows;
			}

		});
		return list;
	}

	// 自动评估
	// 根据[源Id=productId]获得特定范围(限定主目录)的推荐产品(不能重复,而且必须是激活的),采用的推荐规则是：相似价格(默认SKU)
	// tip: sourceId=productId -> mainCategoryId -> ProductCategory -> Product =
	// {product}
	public List<Product> getSimilarPriceProductsBySourceId(
			final Integer sourceId, final int firstResult, final int maxResults) {
		final Product product = (Product) getHibernateTemplate().get(
				Product.class, sourceId);
		if (null == product)
			return new ArrayList<Product>();
		final BigDecimal floorLimitPrice = product.getDefaultProductSku()
				.getPrice().multiply(
						SalesConstants.RECOMMENDED_SIMILAR_PRICE_FLOOR_LIMIT);
		final BigDecimal upperLimitPrice = product.getDefaultProductSku()
				.getPrice().multiply(
						SalesConstants.RECOMMENDED_SIMILAR_PRICE_UPPER_LIMIT);
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer sb = new StringBuffer(200);
				sb.append("select distinct(p) from Product p where ");
				sb.append(" p.status = 1");
				sb.append(" and p.category.categoryPath like :path");
				sb.append(" and p.price between :min and :max");
				sb.append(" and p.productId <> " + sourceId);
				sb.append(" order by p.price desc, p.productId desc");
				Query query = session.createQuery(sb.toString());
				query.setFirstResult(firstResult);
				if (maxResults != -1)
					query.setMaxResults(maxResults);
				query.setString("path", product.getCategory().getCategoryPath() + "%");
				query.setBigDecimal("min", floorLimitPrice);
				query.setBigDecimal("max", upperLimitPrice);
				List<Product> rows = (List<Product>) query.list();

				return rows;
			}

		});
		return list;

	}

	// 自动评估 根据[源Id=productId]获得特定范围(限定主目录)的推荐产品(不能重复,而且必须是激活的),采用的推荐规则是：相同品牌
	// tip:sourceId=productId -> mainCategoryId -> ProductCategory -> Product ->
	// Brand = [product]
	public List<Product> getSameBrandProductsBySourceId(final Integer sourceId,
			final int firstResult, final int maxResults) {
		final Product product = (Product) getHibernateTemplate().get(
				Product.class, sourceId);
		if (null == product || null == product.getBrand())
			return new ArrayList<Product>();
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer sb = new StringBuffer(200);
				sb.append("select distinct(p) from Product p where ");
				sb.append("  p.status = 1");
				sb.append(" and p.category.categoryPath like :path");
				sb.append(" and p.brand.brandId = ").append( product.getBrandId());
				sb.append(" and p.productId <> " + sourceId);
				sb.append(" order by  p.productId desc");
				Query query = session.createQuery(sb.toString());
				query.setString("path", product.getCategory().getCategoryPath() + "%");
				query.setFirstResult(firstResult);
				if (maxResults != -1)
					query.setMaxResults(maxResults);
				List<Product> rows = (List<Product>) query.list();

				return rows;
			}

		});
		return list;
	}
}
